<?php
/**
 * Description of SDimLevel class
 *
 * @author Chisheng Lu, Jeffy Shih
 * @copyright Copyright(c)2010 DoRISE team, Science Education center, NTNU.
 * @since 2010-03-22
 * @version 1.0
 */

class SDimLevel {
    private $mysqli;

    function  __construct() {
        //引入資料庫連結參數設定
        include '../inc/oaConn.inc.php';
        $this->mysqli = @new mysqli($mysqlHost,$mysqlUser,$mysqlPassword,$mysqlDB);
        if(mysqli_connect_errno()) {
            throw new Exception('無法建立資料庫連線！'.mysqli_connect_error());
            $this->mysqli = false;
            exit();
        }

        //連線使用編碼
        $this->mysqli->query("SET NAMES 'utf8'");

    }

    public function getSingleSDimLevel($intSDimLevelId){
        //檢查向度編號是否為空白
        if (empty ($intSDimLevelId)) {
            throw new Exception('缺少層級編號！');
        }

        $sql_selSDimLevel = 'SELECT * FROM `SDimLevel` WHERE `SDLId`=' . $intSDimLevelId;
        echo $sql_selSDimLevel . "<br>";
        $rs_selSDimLevel = $this->mysqli->query($sql_selSDimLevel);

        if(!$rs_selSDimLevel){
            throw new Exception('無法查詢單一層級資料！');
        }else{
            if($rs_selSDimLevel->num_rows == 0){
                throw new Exception('無指定編號的層級資料！');
            }
            $row_selSDimLevel = $rs_selSDimLevel->fetch_row();
            return $row_selSDimLevel;
        }
    }

    public function getSDimLevelListBySDId($intSDId) {

        $arySDimLevels = array();

        $sql_getSDimLevelList = 'SELECT * FROM `SDimLevel` WHERE `SDId` = ' . $intSDId;
        $rs_getSDimLevelList = $this->mysqli->query($sql_getSDimLevelList);

        if(!$rs_getSDimLevelList){
            throw new Exception('無法查詢層級資料！');
        }else{

            while($row_getSDimLevelList = $rs_getSDimLevelList->fetch_row()){
                array_push($arySDimLevels, $row_getSDimLevelList);
            }

            //釋放查詢結果
            $rs_getSDimLevelList->free_result();
        }

        return $arySDimLevels;
    }

    public function addSDimLevel($intSDId, $intSDLOrder, $strEngName, $strChtName, $intUserId) {
        $bolRtn = false;
        //先查詢是否已有相同名稱
        if(empty ($strEngName) || empty($strChtName)){
            throw new Exception('向度名稱不可為空白！');
        }

        $sql_checkDuplicateValue = 'SELECT * FROM `SDimenLevel` WHERE `SDEngName`=\''.$strEngName.'\' OR `SDChtName`=\''.$strChtName.'\''. ' AND SDLOrder = ' . $intSDLOrder. ' AND SDId = ' . $intSDId;
        $rs_checkDuplicateValue = $this->mysqli->query($sql_checkDuplicateValue);

        //檢查是否已有重複值
        if($rs_checkDuplicateValue){
            if($rs_checkDuplicateValue->num_rows != 0){
                throw new Exception('已有相同的層級名稱存在！');
            }
        }
        $strNow = $this->getNowTime();
        $sql_addSDimLevel = 'INSERT INTO `SDimLevel` SET `SDLEngName`=\''.$strEngName.'\', `SDLChtName`=\''.$strChtName.'\', `SDId`='.$intSDId. ', `SDLOrder`='.$intSDLOrder. ', `UserId`='.$intUserId. ', `SDLCreated`=\''.$strNow.'\'';
        $rs_addSDimLevel = $this->mysqli->query($sql_addSDimLevel);
        if(!$rs_addSDimLevel){
            throw new Exception('新增層級資料時發生錯誤！');
        }else{
            $bolRtn = true;
        }
        
        return $bolRtn;
    }

    public function editSDimLevel($intSDimLevelId, $intSDId, $intSDLOrder, $strEngName, $strChtName, $intUserId) {
        $bolRtn = false;
        //檢查是否有層級編號
        if(empty ($intSDimLevelId)){
            throw new Exception('缺少層級編號！');
        }

        //檢查層級名稱是否為空白
        if(empty ($strEngName) || empty ($strChtName)){
            throw new Exception('層級名稱不可為空白！');
        }

        //更新層級資料
        $strNow = $this->getNowTime();
        $sql_uptSDimLevel = 'UPDATE `SDimLevel` SET `SDId`='.$intSDId. ', `SDLOrder`='.$intSDLOrder.', `SDLEngName`=\''.$strEngName.'\', `SDLChtName`=\''.$strChtName.'\', `UserId`='.$intUserId. ', `SDLModified`=\''.$strNow.'\' WHERE `SDLId`='.$intSDimLevelId;
        if(!$this->mysqli->query($sql_uptSDimLevel)){
            throw new Exception('更新層級資料時發生錯誤！');
        }else{
            $bolRtn = true;
        }

        return $bolRtn;
    }

    public function delSDimLevel($intSDimLevelId) {
        $bolRtn = false;
        $intSDId = 0;
        //檢查層級編號是否為空白
        if (empty ($intSDimLevelId)) {
            throw new Exception('缺少層級編號！');
        }
        //檢查層級是否為使用中，使用中不允許刪除
        $sql_chkUsed0 = 'SELECT * FROM `SDimLevel` WHERE `SDLId`=' . $intSDimLevelId;
        $rs_chkUsed0 = $this->mysqli->query($sql_chkUsed0);
        if(!$rs_chkUsed0){
             throw new Exception('異常！查詢層級是否為使用中時發生錯誤。');
        } else {
             if($rs_chkUsed0->num_rows == 0){
                 //throw new Exception('層級項目正在使用中，不允許刪除。');
                 $bolRtn = true;
             }else{
                 $row_getUsed0 = $rs_chkUsed0->fetch_row();
                 $intSDId = $row_getUsed0[0];
                 $sql_chkUsed = 'SELECT * FROM `SQMCDIM` WHERE `DimCode`=' . $intSDId;
                 $rs_chkUsed = $this->mysqli->query($sql_chkUsed);
                 if(!$rs_chkUsed){
                     throw new Exception('異常！查詢層級是否為使用中時發生錯誤。');
                }else{
                    if($rs_chkUsed->num_rows > 0){
                        throw new Exception('層級項目正在使用中，不允許刪除。');
                    }else{
                        //非使用中的向度，先刪除該向度下層級在刪除向度本身
                        $sql_delLevel = 'DELETE FROM `SDimLevel` WHERE `SDLId`=' . $intSDimLevelId;
                        if(!$this->mysqli->query($sql_delLevel)){
                            throw new Exception('錯誤！刪除層級下的層級時發生錯誤。');
                        }else{
                            //刪除向度下層級後再刪除該向度
                            //$sql_delSDimLevel ='DELETE FROM `SDimLevel` WHERE `SDId`=' . $intSDimLevelId;
                            //if(!$this->mysqli->query($sql_delSDimLevel)){
                            //    throw new Exception('錯誤！已刪除向度下層級，於刪除層級時發生錯誤。');
                            //}else{
                            //    $bolRtn = true;
                            //}
                        }
                        $botRtn = true;
                    }
                }
             }
        }

        return $bolRtn;
    }

    private function getNowTime() {
        return date('Y-m-d H:i:s');
    }

    public function getLevelTotalNumById($intSDLId){
        $intLevelCountNum = 0;
        $intSDId = 0;
        if (empty ($intSDimensionId)) {
            throw new Exception('缺少向度編號！');
        }

        $sql_getSDimId = 'SELECT SDId FROM `SDimLevel` WHERE `SDId`=' . $intSDLId;
        $rs_getSDimId = $this->mysqli->query($sql_getSDimId);
        if(!$rs_getSDimId){
            throw new Exception('查詢向度下層級數時發生錯誤！');
        }else{
             if($rs_getSDimId->num_rows > 0){
                $row_getSDimId = $rs_getSDimId->fetch_row();
                $intSPId = $row_getSDimId[0];
                $sql_getLevelNum = 'SELECT Max(SDLOrder) FROM `SDimLevel` WHERE `SDId`=' . $intSDimensionId;
                $rs_getLevelNum = $this->mysqli->query($sql_getLevelNum);
                if(!$rs_getLevelNum){
                    throw new Exception('查詢向度下層級數時發生錯誤！');
                }else{
                    $row_getLevelNum = $rs_getLevelNum->fetch_row();
                    $intLevelCountNum = $row_getLevelNum[0];
                }
             }
        }
        return $intLevelCountNum;
    }

    function  __destruct() {
        if ($this->mysqli) {
            $this->mysqli->close();
            $this->mysqli = FALSE;
        }
    }
}
?>
